﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Collections;

namespace Agresso.Interface.Layer.Service
{
    public class AlertService : IService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        public AlertService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
        }
        public void InsertAlert(Alert alert)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(AlertSQL.InsertAlert(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@alert_type", System.Data.SqlDbType.NVarChar).Value = alert.alert_type;
                cmd.Parameters.Add("@alert_description", System.Data.SqlDbType.NVarChar).Value = alert.alert_description;
                cmd.Parameters.Add("@alert_status", System.Data.SqlDbType.NVarChar).Value = alert.alert_status;
                cmd.Parameters.Add("@alert_date", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@alert_modified_date", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@alert_key", System.Data.SqlDbType.NVarChar).Value = alert.alert_key;
                cmd.Parameters.Add("@entity", System.Data.SqlDbType.NVarChar).Value = alert.entity;
                LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void UpdateAlertStatus(Alert alert)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(AlertSQL.UpdateAlertStatus(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@alert_status", System.Data.SqlDbType.NVarChar).Value = alert.alert_status;
                cmd.Parameters.Add("@alert_modified_date", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@alert_key", System.Data.SqlDbType.NVarChar).Value = alert.alert_key;
                cmd.Parameters.Add("@entity", System.Data.SqlDbType.NVarChar).Value = alert.entity;
                cmd.Parameters.Add("@alert_description", System.Data.SqlDbType.NVarChar).Value = alert.alert_description;
                LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public Alert FindAlertByKey(string key)
        {
            Alert alert = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(AlertSQL.FindAlertByKey(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@alert_key", System.Data.SqlDbType.NVarChar).Value = key;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    alert = new Alert(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return alert;
        }
        public Alert FindAlertByKey(string key, AlertType alertType)
        {
            Alert alert = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(AlertSQL.FindAlertByKeyAndAlertType(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@alert_key", System.Data.SqlDbType.NVarChar).Value = key;
                cmd.Parameters.Add("@alert_type", System.Data.SqlDbType.NVarChar).Value = alertType;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    alert = new Alert(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return alert;
        }
        public IList FindAlertByAlertTypeAndStatus(AlertType alertType, AlertStatus alertStatus )
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(AlertSQL.FindAlertByAlertTypeAndStatus(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@alert_status", System.Data.SqlDbType.NVarChar).Value = alertStatus;
                cmd.Parameters.Add("@alert_type", System.Data.SqlDbType.NVarChar).Value = alertType;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Alert(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList FindAlertByAlertTypeAndStatus(AlertType alertType, AlertStatus alertStatus, string search)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(AlertSQL.FindAlertByAlertTypeAndStatusAndSearch(search), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@alert_status", System.Data.SqlDbType.NVarChar).Value = alertStatus;
                cmd.Parameters.Add("@alert_type", System.Data.SqlDbType.NVarChar).Value = alertType;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Alert(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public void CheckAndUpdateAlert(string key, AlertType alertType, AlertStatus status, string alertMessage, string entity)
        {
            Alert alert = FindAlertByKey(key, alertType);
            if (alert != null)
            {
                alert.alert_status = status;
                alert.alert_description = alertMessage;
                UpdateAlertStatus(alert);
            }
            else 
            {
                if (status == AlertStatus.PENDING)
                    InsertAlert(new Alert(alertType, alertMessage, status, key, entity));
            }
        }
    }
}
